package com.anji.plus.gaea.job.trigger.dao;

import com.anji.plus.gaea.job.core.util.ObjectUtil;
import com.anji.plus.gaea.job.trigger.dao.entity.MetaJobExecutor;
import com.anji.plus.gaea.job.trigger.dao.entity.MetaJobInfo;
import com.anji.plus.gaea.job.trigger.dao.entity.MetaJobLog;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 数据库dao类
 * @author 木子李*de
 * @date 2023-05-18 09:50:46.038
 **/
public class DaoService {
    private static Logger logger = LoggerFactory.getLogger(DaoService.class);

    private static final DaoService instance = new DaoService();

    private JdbcTemplate jdbcTemplate;

    public static void loadJDBC(JdbcTemplate jdbcTemplate){
        instance.jdbcTemplate = jdbcTemplate;
    }

    public static DaoService getInstance(){
        return instance;
    }

    //******************************************* meta_job_executor start *******************************************
    /**
     * 查询meta_job_executor中心跳过期的执行器
     * @param expireTimemillis
     */
    public List<Integer> findExpiredExecutors(Long expireTimemillis) {
        String sql = "SELECT `id` FROM `meta_job_executor` WHERE `expire_timemillis` < ?";
        List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class, expireTimemillis);
        return list;
    }

    public int registryUp(String executorCode, String executorAddress, Long expireTimemillis) {
        String sql = "UPDATE meta_job_executor SET `registory_time` = NOW(), `expire_timemillis` = ?, `available` = 1 WHERE `executor_code` = ? AND `executor_address` = ?";
        int count = jdbcTemplate.update(sql, expireTimemillis, executorCode, executorAddress);
        return count;
    }

    public Integer registrySave(String executorCode, String executorAddress, Long expireTimemillis){
        String sql = "INSERT INTO meta_job_executor( `executor_code` , `executor_address` , `registory_time`, `expire_timemillis`, `available`) VALUES( ?, ?, NOW(), ?, 1)";
        int count = jdbcTemplate.update(sql, executorCode, executorAddress, expireTimemillis);
        return count;
    }

    public Integer registryDown(String executorCode, String executorAddress){
        String sql = "UPDATE meta_job_executor SET `available` = 0 WHERE `executor_code` = ? AND `executor_address` = ?";
        int count = jdbcTemplate.update(sql, executorCode, executorAddress);
        return count;
    }

    public Integer registryDown(Long id){
        String sql = "UPDATE meta_job_executor SET `available` = 0 WHERE `id` = ?";
        int count = jdbcTemplate.update(sql, id);
        return count;
    }

    public List<MetaJobExecutor> queryMetaJobExecutorList(){
        String sql = "select id,executor_code,executor_address,registory_time,expire_timemillis,available from meta_job_executor";
        List<MetaJobExecutor> list = jdbcTemplate.query(sql, MetaJobExecutor.RowMapper);
        return list;
    }

    public List<String> queryMetaJobExecutorUpAddress(String executorCode){
        String sql = "select executor_address from meta_job_executor where `executor_code` = ? AND available = 1 order by id asc";
        List<String> list = jdbcTemplate.queryForList(sql, String.class, executorCode);
        return list;
    }


    //******************************************* meta_job_info start *******************************************
    public MetaJobInfo queryMetaJobInfoById(Long id){
        String sql = "select * from `meta_job_info` where id=?";
        MetaJobInfo metaJobInfo = jdbcTemplate.queryForObject(sql, MetaJobInfo.RowMapper, id);
        return metaJobInfo;
    }

    public List<MetaJobInfo> queryMetaJobInfoInTimeWindow(long maxNextTime, int pagesize){
        String sql = "SELECT * FROM `meta_job_info` WHERE trigger_status=1 AND schedule_type='CRON' AND trigger_next_time<? ORDER BY id ASC LIMIT ?";
        List<MetaJobInfo> list = jdbcTemplate.query(sql, MetaJobInfo.RowMapper, maxNextTime, pagesize);
        return list;
    }

    public int updateMetaJobInfoTriggerTimeStatus(long id, int triggerStatus, long triggerLastTime, long triggerNextTime){
        String sql = "update `meta_job_info` set trigger_status=?, trigger_last_time=?, trigger_next_time=? where id=?";
        int count = jdbcTemplate.update(sql, triggerStatus, triggerLastTime, triggerNextTime, id);
        return count;
    }

    //******************************************* meta_job_info end *******************************************

    //******************************************* meta_job_log start *******************************************
    /**
     * 生成一条日志并返回主键
     * @param jobId
     * @param tenantCode
     * @param orgCode
     * @param executorCode
     * @return jogLogId
     */
    public long saveMetaJobLog(Long jobId, String tenantCode, String orgCode, String executorCode, Date triggerTime){
        String sql = "INSERT INTO meta_job_log (job_id,tenant_code,org_code,executor_code,job_fail_retry_count,trigger_time,trigger_status,executor_status,time_cost) VALUES (?,?,?,?,0,?,0,0,0);";
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator(){
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setLong(1, jobId);
                preparedStatement.setString(2, tenantCode);
                preparedStatement.setString(3, orgCode);
                preparedStatement.setString(4, executorCode);
                preparedStatement.setObject(5, triggerTime);
                return preparedStatement;
            }
        };
        // 获取返回的id
        GeneratedKeyHolder idHolder = new GeneratedKeyHolder();
        int count = jdbcTemplate.update(preparedStatementCreator, idHolder);
        // 主键
        long logId = idHolder.getKey().longValue();
        return logId;
    }

    public int updateMetaJobLogById(MetaJobLog metaJobLog){
        // 参数及类型
        List<Object> args = new ArrayList<Object>();
        List<Integer> argTypeList = new ArrayList<Integer>();

        StringBuffer sql = new StringBuffer("update meta_job_log set ");
        if(metaJobLog.getExecutorId() != null){
            sql.append("executor_id=?, ");
            args.add(metaJobLog.getExecutorId());
            argTypeList.add(Types.BIGINT);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getExecutorCode())){
            sql.append("executor_code=?, ");
            args.add(metaJobLog.getExecutorCode());
            argTypeList.add(Types.VARCHAR);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getExecutorAddress())){
            sql.append("executor_address=?, ");
            args.add(metaJobLog.getExecutorAddress());
            argTypeList.add(Types.VARCHAR);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getJobHandler())){
            sql.append("job_handler=?, ");
            args.add(metaJobLog.getJobHandler());
            argTypeList.add(Types.VARCHAR);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getJobParam())){
            sql.append("job_param=?, ");
            args.add(metaJobLog.getJobParam());
            argTypeList.add(Types.VARCHAR);
        }
        if(metaJobLog.getJobFailRetryCount() != null && metaJobLog.getJobFailRetryCount().intValue() > 0){
            sql.append("job_fail_retry_count=?, ");
            args.add(metaJobLog.getJobFailRetryCount());
            argTypeList.add(Types.INTEGER);
        }
        if(metaJobLog.getTriggerTime() != null){
            sql.append("trigger_time=?, ");
            args.add(metaJobLog.getTriggerTime());
            argTypeList.add(Types.TIMESTAMP);
        }
        if(metaJobLog.getTriggerStatus() != null && metaJobLog.getTriggerStatus().intValue() > 0){
            sql.append("trigger_status=?, ");
            args.add(metaJobLog.getTriggerStatus());
            argTypeList.add(Types.INTEGER);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getTriggerMsg())){
            sql.append("trigger_msg=?, ");
            args.add(metaJobLog.getTriggerMsg());
            argTypeList.add(Types.VARCHAR);
        }
        if(metaJobLog.getExecutorTime() != null){
            sql.append("executor_time=?, ");
            args.add(metaJobLog.getExecutorTime());
            argTypeList.add(Types.TIMESTAMP);
        }
        if(metaJobLog.getExecutorStatus() != null && metaJobLog.getExecutorStatus().intValue() > 0){
            sql.append("executor_status=?, ");
            args.add(metaJobLog.getExecutorStatus());
            argTypeList.add(Types.INTEGER);
        }
        if(ObjectUtil.isNotBlank(metaJobLog.getExecutorMsg())){
            sql.append("executor_msg=?, ");
            args.add(metaJobLog.getExecutorMsg());
            argTypeList.add(Types.VARCHAR);
        }
        if(metaJobLog.getTimeCost() != null && metaJobLog.getTimeCost().longValue() > 0){
            sql.append("time_cost=?, ");
            args.add(metaJobLog.getTimeCost());
            argTypeList.add(Types.BIGINT);
        }

        // 主健
        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" where id=?");
        args.add(metaJobLog.getId());
        argTypeList.add(Types.BIGINT);

        int[] argTypes = argTypeList.stream().mapToInt(Integer::intValue).toArray();

        int count = jdbcTemplate.update(sql.toString(), args.toArray(), argTypes);
        return count;
    }

    /**
     * 调度记录停留在 "运行中" 状态超过10min，且对应执行器心跳注册失败不在线
     * @param lostTime
     * @return
     */
    public List<MetaJobLog> queryLostLogIdFromMetaJogLog(Date lostTime){
        String sql = "select mjl.id,mjl.job_id from meta_job_log mjl left join meta_job_executor mje on mjl.executor_code = mje.executor_code where mjl.trigger_status = 200 and mjl.executor_status = 0 and mjl.trigger_time < ? and mje.available = 0";
        List<MetaJobLog> list = jdbcTemplate.query(sql, MetaJobLog.RowMapper, lostTime);
        //List<Long> list = jdbcTemplate.queryForList(sql, Long.class, lostTime);
        return list;
    }

    public MetaJobLog queryMetaJobLogById(Long id){
        String sql = "select * from `meta_job_log` where id=?";
        MetaJobLog metaJobInfo = jdbcTemplate.queryForObject(sql, MetaJobLog.RowMapper, id);
        return metaJobInfo;
    }
    //******************************************* meta_job_log end *******************************************
}
